<?php
/**
 * 每日销售报表
 * User: bgl
 * Date: 2022/9/20
 * Time: 17:26
 */

namespace App\Admin\Controllers\SalesReport;


use App\Admin\Extensions\Grid\Tool\Export;
use App\Models\Basics\SysArea;
use App\Models\Basics\SysCountry;
use App\Models\Basics\SysPlat;
use App\Models\Inventory\PublicCn;
use App\Models\Product\ProductClassify;
use App\Models\SalesReport\DailySv;
use App\Models\SalesReport\DailyVolume;
use App\Util\User;
use Encore\Admin\Admin;
use Encore\Admin\Controllers\AdminController;
use Encore\Admin\Grid;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Symfony\Component\HttpFoundation\StreamedResponse;

class DailyVolumeController extends AdminController{

    protected $title = 'SKU日销量和库存';

    protected $filters = [
        'sku8' => null,
        'plat_id' => null,
        'area_id' => null,
        'country_id' => null,
        'seller_id' => null,
        'classify_id' => null,
        'season' => null,
        'report_status' => null,
        'less_than' => null,
        'greater_than' => null,
    ];

    protected function grid(){

        $grid = new Grid(new DailySv());

        $filtersField = array_keys($this->filters);
        $filters = $this->_getFilters($filtersField);
        $sku8 = $filters['sku8'];
        $platId = $filters['plat_id'];
        $areaId = $filters['area_id'];
        $countryId = $filters['country_id'];
        $sellerId = $filters['seller_id'];
        $classifyId = $filters['classify_id'];
        $season = $filters['season'];
        $reportStatus = $filters['report_status'];

        $startTime = $filters['greater_than'] ?? date('Y-m-d', strtotime('-31 days'));
        $endTime   = $filters['less_than'] ?? date('Y-m-d', strtotime('-1 days'));
        $endTime = DailySv::checkLessThan($endTime);
        $startTime = DailySv::checkGreaterThan($startTime,$endTime);

        $days = formatDiffDateDay($startTime, $endTime); //天数
        $timeMax = $endTime; //最大可回推时间 (参照时间，最大可生成日期)

        $grid->model()->collection(function (Collection $collection) use($startTime, $endTime, $timeMax, $days) {
            foreach($collection as $item) {
                $item->seller_id = User::getUsernameById($item->seller_id);
                $dayVolumeArray = DailyVolume::getSkuDaysVolume($startTime, $endTime, $item->plat_id, $item->psku_id, $item->country_id);
                $daysTotal = 0;
                for ($i = $days; $i >= 0; $i--) {
                    $dateTitle = date("Y-m-d", strtotime("$timeMax -$i day")); //赋值,日期格式 (1970-01-01)
                    if (array_key_exists($dateTitle, $dayVolumeArray)) {
                        $item->{$dateTitle} = $dayVolumeArray[$dateTitle]; //赋值,销量(当天)
                        $daysTotal += $item->{$dateTitle};
                    }else{
                        $item->{$dateTitle} = 0;
                    }
                }
                $item->volume = $daysTotal; //区间总量
                if ($item->plat_id == SysPlat::PLAT_AMAZON) {
                    $item->cnp_instant_num = PublicCn::getInstant($item->psku_id); //公共在库
                }else{
                    $item->cnp_instant_num = 0; //公共在库
                }
                $item->inventory_num = $item->fba_instant_num +
                                       $item->fba_transit_num +
                                       $item->cn_instant_num +
                                       $item->cn_transit_num +
                                       $item->os_instant_num +
                                       $item->os_transit_num; //库存(和)
                $item->inv_factor = DailySv::getInvFactor($item); //库存系数
            }
            return $collection;
        });

        $grid->column('plat.title','平台')->totalRow('TTL');
        $grid->column('area.area','站点');
        $grid->column('country.country','国家');
        $grid->column('sku.psku_code','SKU');
        $grid->column('sku.psku_cn','品名');
        $grid->column('classify.season','季节性')->using(ProductClassify::$_season);
        $grid->column('classify.title','品类');
        $grid->column('seller_id','销售');
        $grid->column('volume', '销量和');

        //依据时间条件,生成指定天数
        for($i = $days; $i >= 0; $i--){
            $dayTitle = date("Y-m-d", strtotime("$timeMax -$i day"));
            $grid->column($dayTitle, $dayTitle); //日期格式 (1970-01-01)
            //当日的销量汇总
            $skuDayVolumeSum = DailyVolume::getSkuDayVolumeSum($dayTitle, $sku8, $platId, $countryId, $areaId, $sellerId, $classifyId, $season, $reportStatus);
            $script = <<<SCRIPT
                $(function($){
                    $(".table tfoot:first").css({'color':'red'});
                    $(".table tfoot:first").find('.column-{$dayTitle}').text({$skuDayVolumeSum});
                });
SCRIPT;
            Admin::script($script);
        }
        //时间段内的销量汇总
        $skuDaysVolumeSum = DailyVolume::getSkuDaysVolumeSum($startTime,$endTime,$sku8,null,$platId,$countryId,$areaId,$sellerId,$classifyId,$season,$reportStatus);
        //获取平均销量&库存汇总
        $avgAndInventorySum = DailySv::getAvgDayAndInventorySum($sku8,$platId,$countryId,$areaId,$sellerId,$classifyId,$season,$reportStatus);
        $avg3Sum = $avgAndInventorySum->avg3 ?: 0;
        $avg7Sum = $avgAndInventorySum->avg7 ?: 0;
        $avg15Sum = $avgAndInventorySum->avg15 ?: 0;
        $avg30Sum = $avgAndInventorySum->avg30 ?: 0;
        $salesStatus = DailySv::getSalesStatus($avg3Sum, $avg7Sum, $avg15Sum, $avg30Sum);
        $avgSum = DailySv::getWeightAvg($avg3Sum, $avg7Sum, $avg15Sum, $avg30Sum, $salesStatus);
        $salesStatusValue = DailySv::$_salesStatus[$salesStatus]; //销售趋势(总计)
        $fbaInstantSum = $avgAndInventorySum->fba_instant_num ?: 0;
        $fbaTransitSum = $avgAndInventorySum->fba_transit_num ?: 0;
        $cnInstantSum = $avgAndInventorySum->cn_instant_num ?: 0;
        $cnTransitSum = $avgAndInventorySum->cn_transit_num ?: 0;
        $osInstantSum = $avgAndInventorySum->os_instant_num ?: 0;
        $osTransitSum = $avgAndInventorySum->os_transit_num ?: 0;
        $inventoryNumSum = $fbaInstantSum + $fbaTransitSum + $cnInstantSum + $cnTransitSum + $osInstantSum + $osTransitSum;
        $invFactorSum = DailySv::getInvFactorString($fbaInstantSum,$fbaTransitSum,$cnInstantSum,$cnTransitSum,$osInstantSum,$osTransitSum,$avgSum);
        //获取公共库存汇总
        $cnpInstantSum = PublicCn::getInstantSum($sku8, $classifyId, $season, $reportStatus);
        //格式化显示平均销量
        $avg3Sum = round($avg3Sum);
        $avg7Sum = round($avg7Sum);
        $avg15Sum = round($avg15Sum);
        $avg30Sum = round($avg30Sum);
        $avgSum = round($avgSum);
        $script = <<<SCRIPT
            $(function($){
                $(".table tfoot").css({'color':'red'});
                $(".table tfoot:first").find('.column-volume').text({$skuDaysVolumeSum});

                $(".table tfoot:first").find('.column-avg3').text({$avg3Sum});
                $(".table tfoot:first").find('.column-avg7').text({$avg7Sum});
                $(".table tfoot:first").find('.column-avg15').text({$avg15Sum});
                $(".table tfoot:first").find('.column-avg30').text({$avg30Sum});
                $(".table tfoot:first").find('.column-avg').text({$avgSum});
                $(".table tfoot:first").find('.column-sales_status').text('{$salesStatusValue}');

                $(".table tfoot:first").find('.column-cnp_instant_num').text({$cnpInstantSum});
                $(".table tfoot:first").find('.column-fba_instant_num').text({$fbaInstantSum});
                $(".table tfoot:first").find('.column-fba_transit_num').text({$fbaTransitSum});
                $(".table tfoot:first").find('.column-cn_instant_num').text({$cnInstantSum});
                $(".table tfoot:first").find('.column-cn_transit_num').text({$cnTransitSum});
                $(".table tfoot:first").find('.column-os_instant_num').text({$osInstantSum});
                $(".table tfoot:first").find('.column-os_transit_num').text({$osTransitSum});
                $(".table tfoot:first").find('.column-inventory_num').text({$inventoryNumSum});
                $(".table tfoot:first").find('.column-inv_factor').html('{$invFactorSum}');
            });
SCRIPT;
        Admin::script($script);

        $grid->column('avg3', '3天AVG')->display(function ($value){
            return round($value);
        });
        $grid->column('avg7', '7天AVG')->display(function ($value){
            return round($value);
        });
        $grid->column('avg15','15天AVG')->display(function ($value){
            return round($value);
        });
        $grid->column('avg30','30天AVG')->display(function ($value){
            return round($value);
        });
        $grid->column('avg','AVG')->display(function ($value){
            return round($value);
        });
        $grid->column('sales_status','销售趋势')->display(function ($value){
            return DailySv::$_salesStatus[$value];
        });
        $grid->column('cnp_instant_num','公共在库');
        $grid->column('fba_instant_num', 'FBA在库');
        $grid->column('fba_transit_num', 'FBA在途');
        $grid->column('cn_instant_num','国内在库');
        $grid->column('cn_transit_num','国内在途');
        $grid->column('os_instant_num','海外仓在库');
        $grid->column('os_transit_num','海外仓在途');
        $grid->column('inventory_num','库存和');
        $grid->column('inv_factor','FBA&ALL库存系数');

        $grid->disableExport();
        $grid->tools(function (Grid\Tools $tools){
            $tools->append(new Export('sales-report/daily-volume-download'));
        });

        $grid->fixColumns(5, 0);
        $grid->paginate(10);
        $grid->expandFilter();
        $grid->disableActions();
        $grid->disableRowSelector();
        $grid->disableCreateButton();
        $grid->filter(function($filter){
            $filter->column(1/2, function ($filter) {
                $filter->where(function ($query) {
                    $query->whereHas('sku', function ($query) {
                        $query->where('psku_code', 'like', "%{$this->input}%")
                            ->orWhere('psku_cn', 'like', "%{$this->input}%");
                    });
                }, 'SKU','sku8')->placeholder('模糊查询SKU，名称');
                $filter->gt('greater_than', '日期')->date();
                $filter->lt('less_than', '日期')->date();
                $filter->equal('seller_id', '销售')->select(User::getUserOptions());
                $filter->equal('classify.report_status', '开放')->radio(ProductClassify::$_reportStatus+[ '' => 'All']);
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('plat_id','平台')->select(SysPlat::$_platformNormal);
                $filter->equal('area_id','站点')->select(SysArea::options());
                $filter->equal('country_id','国家')->select(SysCountry::options());
                $filter->equal('classify.season', '季节性')->select(ProductClassify::$_season);
                $filter->equal('classify_id', '品类')->select(ProductClassify::options());
            });
        });
        return $grid;
    }

    private function _getFilters($filtersField){
        $filters = [];
        foreach ($filtersField as $field){
            if($field == 'season'){
                $classify = request('classify');
                $filters[$field] = isset($classify[$field]) ? $classify[$field] : null;
            }elseif($field == 'report_status'){
                $classify = request('classify');
                $filters[$field] = isset($classify[$field]) ? $classify[$field] : null;
            }else{
                $filters[$field] = request($field);
            }
        }
        return $filters;
    }

    public function download(){
        $filtersField = array_keys($this->filters);
        $filters = $this->_getFilters($filtersField);
        $sku = $filters['sku8'];
        $platId = $filters['plat_id'];
        $areaId = $filters['area_id'];
        $countryId = $filters['country_id'];
        $sellerId = $filters['seller_id'];
        $classifyId = $filters['classify_id'];
        $season = $filters['season'];
        $reportStatus = $filters['report_status'];

        $startTime = $filters['greater_than'] ?? date('Y-m-d', strtotime('-31 days'));
        $endTime   = $filters['less_than'] ?? date('Y-m-d', strtotime('-1 day'));
        $endTime = DailySv::checkLessThan($endTime);
        $startTime = DailySv::checkGreaterThan($startTime,$endTime);

        $queryExport = DailySv::query();
        if ($platId) $queryExport->where('plat_id', $platId);
        if ($areaId) $queryExport->where('area_id', $areaId);
        if ($countryId) $queryExport->where('country_id', $countryId);
        if ($sellerId) $queryExport->where('seller_id', $sellerId);
        if ($classifyId) $queryExport->where('classify_id', $classifyId);
        if ($sku) {
            $queryExport->whereHas('sku', function ($query) use($sku){
                $query->where('psku_code', 'like', "%{$sku}%")
                    ->orWhere('psku_cn', 'like', "%{$sku}%");
            });
        }
        if ($season) {
            $queryExport->whereHas('classify', function ($query) use($season){
                $query->where('season', $season);
            });
        }
        if ($reportStatus) {
            $queryExport->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }

        $this->_exeDownload($queryExport, $startTime, $endTime);
    }

    private function _exeDownload($queryExport, $startTime, $endTime){
        $days = formatDiffDateDay($startTime, $endTime); //天数
        $timeMax = $endTime; //最大可回推时间 (参照时间，最大可生成日期)
        $title1 = ['平台','站点','国家','SKU','品名','品类','季节性','销售','销量和'];
        $title2 = [];
        for($i = $days; $i >= 0; $i--){
            $title2[] = date("Y.m.d", strtotime("$timeMax -$i day"));
        }
        $title3 = ['3天AVG','7天AVG','15天AVG','30天AVG','AVG','销售趋势','公共在库','FBA在库','FBA在途','国内在库','国内在途','海外仓在库','海外仓在途','库存和','FBA&ALL库存系数'];
        $head = array_merge($title1, $title2, $title3);
        set_time_limit(0);
        $response = new StreamedResponse(null, 200, [
            'Content-Type'        => 'text/csv;charset=utf-8',
            'Content-Disposition' => 'attachment;filename="SKU日销量和库存.csv"',
        ]);
        $response->setCallback(function () use ($head, $queryExport, $days, $startTime, $endTime, $timeMax) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $queryExport->chunk(500, function ($re) use ($fp, $days, $startTime, $endTime, $timeMax){
                $data = DailySv::getDownloadData($re, $days, $startTime, $endTime, $timeMax);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
        $response->send();
        exit(0);
    }
}
